package com.example.tms.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.tms.bean.*;
import com.example.tms.mapper.*;
import com.example.tms.tools.GlobalParam;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;


import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.print.Book;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.data.domain.Page;
import com.example.tms.bean.WaybillVMgmt.WaybillQuery;
import com.example.tms.annotation.*;



/**
 * 运单管理
 */
@RestController
public class WaybillController {

    private final WaybillMapper waybillMapper;
    private final OrderMapper orderMapper;
    private final CarVMapper carVMapper;
    private final OilfillMapper oilfillMapper;
    private final CstmrMapper cstmrMapper;
    private final AreaMapper areaMapper;
    private final RoadMapper roadMapper;
    private final CarMapper carMapper;
    private final WaybillVMapper waybillVMapper;


    @Autowired
    public WaybillController(WaybillMapper waybillMapper, OrderMapper orderMapper, CarVMapper carVMapper, OilfillMapper oilfillMapper, CstmrMapper cstmrMapper, AreaMapper areaMapper, RoadMapper roadMapper, WaybillVMapper waybillVMapper, CarMapper carMapper) {
        this.waybillMapper = waybillMapper;
        this.orderMapper = orderMapper;
        this.carVMapper = carVMapper;
        this.oilfillMapper = oilfillMapper;
        this.cstmrMapper = cstmrMapper;
        this.areaMapper = areaMapper;
        this.roadMapper = roadMapper;
        this.carMapper = carMapper;
        this.waybillVMapper = waybillVMapper;
    }

    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    @PersistenceContext
    EntityManager entityManager;
    //查询维修信息列表兼搜索
    @RequestMapping(value = "waybill", method = RequestMethod.GET)
    public ReturnInfo  getWaybillList1(@RequestParam(value = "pageNum", defaultValue = "1") String pageNum,
                                             @RequestParam(value = "pageSize", defaultValue = "0") String pageSize,
                                             @RequestParam(required = false, defaultValue = "") String ylzl,
                                             @RequestParam(required = false, defaultValue = "") String cph,
                                             @RequestParam(required = false, defaultValue = "") String jsyxm,
                                             @RequestParam(required = false, defaultValue = "") String cllx,
                                             @RequestParam(required = false, defaultValue = "") String khjc,
                                             @RequestParam(required = false, defaultValue = "") String khzjm,
                                             @RequestParam(required = false, defaultValue = "") String xlmc,
                                             @RequestParam(required = false, defaultValue = "") String xlzjm,
                                             @RequestParam(required = false, defaultValue = "") String qymc,
                                             @RequestParam(required = false, defaultValue = "") String qyzjm,
                                             @RequestParam(required = false, defaultValue = "") String ydh,
                                             @RequestParam(required = false, defaultValue = "") String ydzt,
                                             @RequestParam(required = false, defaultValue = "") String qsrq,
                                             @RequestParam(required = false, defaultValue = "") String jsrq,
                                             @RequestParam(required = false, defaultValue = "") String bd){

        StringBuilder countSelectSql = new StringBuilder();
        countSelectSql.append("select count(1) from WaybillMgmt where 1=1 ");

        StringBuilder selectSql = new StringBuilder();
        selectSql.append("from WaybillVMgmt where 1=1 ");

        Map<String,Object> params = new HashMap<>();
        StringBuilder whereSql = new StringBuilder();
        if (!ylzl.equals("")) {
            whereSql.append(" and ylzl like CONCAT('%',:ylzl,'%') ");
            params.put("ylzl",ylzl);
        }
        if (!cph.equals("")) {
            whereSql.append(" and cph like CONCAT('%',:cph,'%') ");
            params.put("cph",cph);
        }
        if (!cllx.equals("")) {
            whereSql.append(" and leixing like CONCAT('%',:leixing,'%') ");
            params.put("leixing",cllx);
        }

        if (!jsyxm.equals("")) {
            whereSql.append(" and jsyxm like CONCAT('%',:jsyxm,'%') ");
            params.put("jsyxm",jsyxm);
        }
        if (!khjc.equals("")) {
            whereSql.append(" and khjc like CONCAT('%',:khjc,'%') ");
            params.put("khjc",khjc);
        }
        if (!khzjm.equals("")) {
            whereSql.append(" and khzjm like CONCAT('%',:khzjm,'%') ");
            params.put("khzjm",khzjm);
        }
        if (!xlmc.equals("")) {
            whereSql.append(" and xlmc like CONCAT('%',:xlmc,'%') ");
            params.put("xlmc",xlmc);
        }
        if (!xlzjm.equals("")) {
            whereSql.append(" and xlzjm like CONCAT('%',:xlzjm,'%') ");
            params.put("xlzjm",xlzjm);
        }
        if (!qymc.equals("")) {
            whereSql.append(" and qymc like CONCAT('%',:qymc,'%') ");
            params.put("qymc",qymc);
        }
        if (!qyzjm.equals("")) {
            whereSql.append(" and qyzjm like CONCAT('%',:qyzjm,'%') ");
            params.put("qyzjm",qyzjm);
        }
        if (!ydh.equals("")) {
            whereSql.append(" and ydh like CONCAT('%',:ydh,'%') ");
            params.put("ydh",ydh);
        }
        if (!ydzt.equals("")) {
            whereSql.append(" and ydzt like CONCAT('%',:ydzt,'%') ");
            params.put("ydzt",ydzt);
        }
        if (!qsrq.equals("")) {
            whereSql.append(" and fcrq >= :qsrq");
            params.put("qsrq", qsrq);
        }
        if (!jsrq.equals("")) {
            whereSql.append(" and fcrq <= :jsrq");
            params.put("jsrq", jsrq);
        }
        switch (bd){
            case "dd":
                whereSql.append(" and ydzt in ('待调度','待发车','已发车','调度_审核中','订单_审核中') ");
                break;
            case "dj":
                whereSql.append(" and ydzt in ('已发车','已回车','登记_审核中','已完成','已报账') ");
                break;
            case "bz":
                whereSql.append(" and ydzt in ('已回车','已完成','报账_审核中','已发车','已报账') ");
                break;
            case "kj":
                whereSql.append(" and ydzt in ('快捷_已报','快捷_审核中' ) ");
                break;
            default:
                break;
        }
        whereSql.append(" and sfcz = true ");
        String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();
        TypedQuery<Long> countQuery = this.entityManager.createQuery(countSql,Long.class);
        this.setParameters(countQuery,params);
        Long count = (Long) countQuery.getSingleResult();

        String querySql = new StringBuilder().append(selectSql).append(whereSql).toString();
        Query query = this.entityManager.createQuery(querySql,WaybillVMgmt.class);
        this.setParameters(query,params);
        if(pageNum != null && pageSize != null){ //分页
            query.setFirstResult((Integer.valueOf(pageNum)-1)*(Integer.valueOf(pageSize)));
            query.setMaxResults(Integer.valueOf(pageSize));
        }
        logger.info("cllx:"+cllx+"cph:"+cph+"sjxm"+jsyxm);
        List<WaybillVMgmt> WaybillVMgmtList = query.getResultList();
        return  ReturnInfo.info5(count.toString(),WaybillVMgmtList,0,"");
    }

    /**
     * 给hql参数设置值
     * @param query 查询
     * @param params 参数
     */
    private void setParameters(Query query,Map<String,Object> params){
        for(Map.Entry<String,Object> entry:params.entrySet()){
            query.setParameter(entry.getKey(),entry.getValue());
        }
    }

    //查询运单信息列表
    @RequestMapping(value = "waybilllist/dd", method = RequestMethod.GET)
    public ReturnInfo getWaybillListDd(@RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findDdBysfcz(true);
        return ReturnInfo.info2(result);
    }

    //查询运单信息列表
    @RequestMapping(value = "waybilllist/dj", method = RequestMethod.GET)
    public ReturnInfo getWaybillListDj(@RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findDjBysfcz(true);
        return ReturnInfo.info2(result);
    }

    //查询运单信息列表
    @RequestMapping(value = "waybilllist/bz", method = RequestMethod.GET)
    public ReturnInfo getWaybillListBz(@RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findBzBysfcz(true);
        return ReturnInfo.info2(result);
    }

    //查询运单信息列表
    @RequestMapping(value = "waybilllist/kj", method = RequestMethod.GET)
    public ReturnInfo getWaybillListKj(@RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findKjBysfcz(true);
        return ReturnInfo.info2(result);
    }

    //查询运单信息列表
    @RequestMapping(value = "waybilllist/all", method = RequestMethod.GET)
    public ReturnInfo getWaybillListAll(@RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findAllBysfcz(true);
        return ReturnInfo.info2(result);
    }

    public List<WaybillVMgmt> getWaybillListexcel(@RequestParam(required = false, defaultValue = "") String ylzl,
                                     @RequestParam(required = false, defaultValue = "") String cph,
                                     @RequestParam(required = false, defaultValue = "") String jsyxm,
                                     @RequestParam(required = false, defaultValue = "") String khjc,
                                     @RequestParam(required = false, defaultValue = "") String khzjm,
                                     @RequestParam(required = false, defaultValue = "") String xlmc,
                                     @RequestParam(required = false, defaultValue = "") String xlzjm,
                                     @RequestParam(required = false, defaultValue = "") String qymc,
                                     @RequestParam(required = false, defaultValue = "") String qyzjm,
                                     @RequestParam(required = false, defaultValue = "") String ydh,
                                     @RequestParam(required = false, defaultValue = "") String ydzt,
                                     @RequestParam(required = false, defaultValue = "") String qsrq,
                                     @RequestParam(required = false, defaultValue = "") String jsrq,
                                     @RequestParam(required = false, defaultValue = "") String bd) {
        List<WaybillVMgmt> result = waybillVMapper.findAll((Root<WaybillVMgmt> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {
            List<Predicate> list = new ArrayList<>();
            if (!ylzl.equals("")) {
                list.add(criteriaBuilder.like(root.get("ylzl").as(String.class), "%" + ylzl + "%"));
            }
            if (!cph.equals("")) {
                list.add(criteriaBuilder.like(root.get("cph").as(String.class), "%" + cph + "%"));
            }
            if (!jsyxm.equals("")) {
                list.add(criteriaBuilder.like(root.get("jsyxm").as(String.class), "%" + jsyxm + "%"));
            }
            if (!khjc.equals("")) {
                list.add(criteriaBuilder.like(root.get("khjc").as(String.class), "%" + khjc + "%"));
            }
            if (!khzjm.equals("")) {
                list.add(criteriaBuilder.like(root.get("khzjm").as(String.class), "%" + khzjm + "%"));
            }
            if (!xlmc.equals("")) {
                list.add(criteriaBuilder.like(root.get("xlmc").as(String.class), "%" + xlmc + "%"));
            }
            if (!xlzjm.equals("")) {
                list.add(criteriaBuilder.like(root.get("xlzjm").as(String.class), "%" + xlzjm + "%"));
            }
            if (!qymc.equals("")) {
                list.add(criteriaBuilder.like(root.get("qymc").as(String.class), "%" + qymc + "%"));
            }
            if (!qyzjm.equals("")) {
                list.add(criteriaBuilder.like(root.get("qyzjm").as(String.class), "%" + qyzjm + "%"));
            }
            if (!ydh.equals("")) {
                list.add(criteriaBuilder.like(root.get("ydh").as(String.class), "%" + ydh + "%"));
            }
            if (!ydzt.equals("")) {
                list.add(criteriaBuilder.like(root.get("ydzt").as(String.class), "%" + ydzt + "%"));
            }
            if (!qsrq.equals("")) {
                list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("fcrq").as(String.class), qsrq));
            }
            if (!jsrq.equals("")) {
                list.add(criteriaBuilder.lessThanOrEqualTo(root.get("fcrq").as(String.class), jsrq));
            }
            list.add(criteriaBuilder.equal(root.get("sfcz").as(Boolean.class), true));
            Predicate[] p = new Predicate[list.size()];
            Predicate predicate = criteriaBuilder.and(list.toArray(p));
            switch (bd){
                case "dd":
                    predicate = criteriaBuilder.and(predicate,criteriaBuilder.or(criteriaBuilder.equal(root.get("ydzt"),"待调度"), criteriaBuilder.equal(root.get("ydzt"),"待发车"), criteriaBuilder.equal(root.get("ydzt"),"已发车"), criteriaBuilder.equal(root.get("ydzt"),"调度_审核中"), criteriaBuilder.equal(root.get("ydzt"),"订单_审核中")));
                    break;
                case "dj":
                    predicate = criteriaBuilder.and(predicate,criteriaBuilder.or(criteriaBuilder.equal(root.get("ydzt"),"已发车"), criteriaBuilder.equal(root.get("ydzt"),"已回车"), criteriaBuilder.equal(root.get("ydzt"),"登记_审核中"),criteriaBuilder.equal(root.get("ydzt"),"已完成"),criteriaBuilder.equal(root.get("ydzt"),"已报账")));
                    break;
                case "bz":
                    predicate = criteriaBuilder.and(predicate,criteriaBuilder.or(criteriaBuilder.equal(root.get("ydzt"),"已回车"), criteriaBuilder.equal(root.get("ydzt"),"已完成"), criteriaBuilder.equal(root.get("ydzt"),"报账_审核中"),criteriaBuilder.equal(root.get("ydzt"),"已发车"),criteriaBuilder.equal(root.get("ydzt"),"已报账")));
                    break;
                case "kj":
                    predicate = criteriaBuilder.and(predicate,criteriaBuilder.or(criteriaBuilder.equal(root.get("ydzt"),"快捷_已报"), criteriaBuilder.equal(root.get("ydzt"),"快捷_审核中")));
                    break;
                default:
                    break;
            }
            criteriaQuery.where(predicate);
            criteriaQuery.orderBy(criteriaBuilder.desc(root.get("ydh")),criteriaBuilder.desc(root.get("khjc")));
            return criteriaQuery.getRestriction();
        });
        return result;
    }
    //查询运单信息
    @RequestMapping(value = "waybill/{ydid}", method = RequestMethod.GET)
    public WaybillVMgmt getWaybillInfo(@PathVariable String ydid) {
        return waybillVMapper.findByYdid(ydid);
    }

    //车辆调度（车牌号）
    @MonitorRequest
    @RequiresPermissions("222")
    @RequestMapping(value = "waybill/cph", method = RequestMethod.PUT)
    public ReturnInfo updateWaybillCph(@RequestBody WaybillMgmt w){
        try{
            List<Map<String,Object>>  list = new ArrayList<>();
            Map<String,Object>  jsy = new HashMap<>();
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());

            if(w.getClid().equals("")){
                waybillMgmt = WaybillMgmt.Waybill(waybillMgmt);
            }else {
                CarView carView = carVMapper.findByClidAndSfcz(w.getClid(),true);
                if(!waybillMgmt.getLeixing().equals("")){
                    waybillMgmt = WaybillMgmt.Waybill(waybillMgmt,carView);
                    waybillMgmt.setYdzt("待发车");
                }else {
                    waybillMgmt = WaybillMgmt.Waybill(waybillMgmt,carView);
                }
            }
            waybillMapper.saveAndFlush(waybillMgmt);
            WaybillMgmt waybillMgmt1 = waybillMapper.findByYdid(w.getYdid());
            if(waybillMgmt1.getJsyxm()!=null&&!waybillMgmt1.getJsyxm().equals("")){
                jsy.put("jsyxm",waybillMgmt.getJsyxm());
            }
            if(waybillMgmt1.getJsysj()!=null&&!waybillMgmt1.getJsysj().equals("")){
                jsy.put("jsysj",waybillMgmt.getJsysj());
            }
            jsy.put("ydzt",waybillMgmt1.getYdzt());
            logger.info("车辆调度成功"+w.getYdid());
            return ReturnInfo.info2(jsy);
        }catch (NullPointerException e){
            logger.info("车辆调度失败"+w.getYdid());
            return ReturnInfo.info1("车辆调度失败！");
        }
    }

    //更新类型
    @MonitorRequest
    @RequestMapping(value = "waybill/type", method = RequestMethod.PUT)
    public ReturnInfo updateWaybillType(@RequestBody WaybillMgmt w){
        try{
            List<Map<String,Object>>  list = new ArrayList<>();
            Map<String,Object>  jsy = new HashMap<>();
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
                if(w.getLeixing().equals("")){
                    waybillMgmt.setYdzt("待调度");
                    waybillMgmt.setLeixing(w.getLeixing());
                }else {
                    if(waybillMgmt.getCph().equals("")){
                        waybillMgmt.setLeixing(w.getLeixing());
                    }else {
                        waybillMgmt.setYdzt("待发车");
                        waybillMgmt.setLeixing(w.getLeixing());
                    }
                }
            waybillMapper.saveAndFlush(waybillMgmt);
            WaybillMgmt waybillMgmt1 = waybillMapper.findByYdid(w.getYdid());
            jsy.put("ydzt",waybillMgmt1.getYdzt());
            logger.info("车辆调度成功"+w.getYdid());
            return ReturnInfo.info2(jsy);
        }catch (NullPointerException e){
            logger.info("车辆调度失败"+w.getYdid());
            return ReturnInfo.info1("车辆调度失败！");
        }
    }

    //更新日期
    @MonitorRequest
    @RequiresPermissions("223")
    @RequestMapping(value = "waybill/fcrq", method = RequestMethod.PUT)
    public ReturnInfo updateWaybillRq(@RequestBody WaybillMgmt w){
        try{
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            if(w.getFcrq()!=null&&!w.getFcrq().equals("")){
                waybillMgmt.setFcrq(w.getFcrq());
            }else {
                waybillMgmt.setFcrq(GlobalParam.getSRq());
            }
            waybillMgmt.setFcrq(w.getFcrq());
            waybillMapper.saveAndFlush(waybillMgmt);
            logger.info("运单发车成功"+w.getYdid());
            return ReturnInfo.info1("运单发车成功！");
        }catch (NullPointerException e){
            logger.info("运单发车失败"+w.getYdid());
            return ReturnInfo.info1("运单发车失败！");
        }
    }
    //运单发车
    @RequiresPermissions("223")
    @RequestMapping(value = "waybill/{ydid}", method = RequestMethod.PUT)
    public ReturnInfo startWaybill(@PathVariable("ydid") String ydid){
        String b;
        if(waybillMapper.findByYdid(ydid)==null){
            b = "运单不存在";
        }else {
            waybillMapper.startWaybill(ydid);//发车
            b = "发车成功！";
//            carMapper.updateCarfp(waybillMgmt.getCph());
            logger.info("运单发车成功"+ydid);
        }
        return ReturnInfo.info1(b);
    }
    //运单添加备注
    @RequestMapping(value = "waybill/tjbz", method = RequestMethod.PUT)
    public ReturnInfo addRemarks(@RequestBody WaybillMgmt w){
        try{
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            if(w.getYdbz()!=null){
                waybillMgmt.setYdbz(w.getYdbz());
            }
            waybillMapper.saveAndFlush(waybillMgmt);
            logger.info("运单添加备注成功"+w.getYdid());
            return ReturnInfo.info1("运单添加备注成功！");
        }catch (NullPointerException e){
            logger.info("运单添加备注失败"+w.getYdid());
            return ReturnInfo.info1("运单添加备注失败！");
        }
    }


    //回车登记
    @MonitorRequest
    @RequiresPermissions("232")
    @RequestMapping(value = "register", method = RequestMethod.POST)
    public List<Map<String,Object>> registerWaybill(@RequestBody WaybillMgmt w){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(w.getYdid()==null){
            map.put("ydid","0");
        }else if(waybillMapper.findByYdid(w.getYdid())==null){
            map.put("ydid","1");
        }
        list.add(map);
        if(map.isEmpty()){
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            waybillMgmt = WaybillMgmt.register(waybillMgmt,w);
            waybillMapper.saveAndFlush(waybillMgmt);
           // carMapper.updateBackCarfp(waybillMgmt.getCph());
            logger.info("回车登记成功"+w.getYdid());
             // 修改订单状态
            if(orderMapper.findByDdhAndSfcz(w.getYdh(),true)!=null){
                orderMapper.UpdateByDdidzt(w.getYdh());
            }
        }
        return list;
    }

    //回车报账
    @MonitorRequest
    @RequiresPermissions("242")
    @RequestMapping(value = "account", method = RequestMethod.POST)
    public List<Map<String,Object>> accountWaybill(@RequestBody WaybillMgmt w){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(w.getYdid()==null){
            map.put("ydid","0");
        }else if(waybillMapper.findByYdid(w.getYdid())==null){
            map.put("ydid","1");
        }
        if(!w.getSrqt().equals("")&&!checkNum(w.getSrqt())){
            map.put("srqt", "0");
        }
        if(!w.getHuihuo().equals("")&&!checkNum(w.getHuihuo())){
            map.put("huihuo", "0");
        }
        if(!w.getHhbh().equals("")&&!checkNum(w.getHhbh())){
            map.put("hhbh", "0");
        }
        if(!w.getSryf().equals("")&&!checkNum(w.getSryf())){
            map.put("sryf", "0");
        }
        if(!w.getZsr().equals("")&&!checkNum(w.getZsr())){
            map.put("zsr", "0");
        }
        if(!w.getSryk().equals("")&&!checkNum(w.getSryk())){
            map.put("sryk", "0");
        }
        if(!w.getSrcy().equals("")&&!checkNum(w.getSrcy())){
            map.put("srcy", "0");
        }
        if(!w.getGlf().equals("")&&!checkNum(w.getGlf())){
            map.put("glf", "0");
        }
        if(!w.getXjjy().equals("")&&!checkNum(w.getXjjy())){
            map.put("xjjy", "0");
        }
        if(!w.getBuzhu().equals("")&&!checkNum(w.getBuzhu())){
            map.put("buzhu", "0");
        }
        if(!w.getMaidan().equals("")&&!checkNum(w.getMaidan())){
            map.put("maidan", "0");
        }
        if(!w.getSgcl().equals("")&&!checkNum(w.getSgcl())){
            map.put("sgcl", "0");
        }
        if(!w.getTcf().equals("")&&!checkNum(w.getTcf())){
            map.put("tcf", "0");
        }
        if(!w.getZsf().equals("")&&!checkNum(w.getZsf())){
            map.put("zsf", "0");
        }
        if(!w.getZxf().equals("")&&!checkNum(w.getZxf())){
            map.put("zxf", "0");
        }
        if(!w.getChuanpiao().equals("")&&!checkNum(w.getChuanpiao())){
            map.put("chuanpiao", "0");
        }
        if(!w.getWzfk().equals("")&&!checkNum(w.getWzfk())){
            map.put("wzfk", "0");
        }
        if(!w.getPeijian().equals("")&&!checkNum(w.getPeijian())){
            map.put("peijian", "0");
        }
        if(!w.getZcqt().equals("")&&!checkNum(w.getZcqt())){
            map.put("zcqt", "0");
        }
        if(!w.getWxfycn().equals("")&&!checkNum(w.getWxfycn())){
            map.put("wxfycn", "0");
        }
        if(!w.getWxfycw().equals("")&&!checkNum(w.getWxfycw())){
            map.put("wxfycw", "0");
        }
        if(!w.getZcsx().equals("")&&!checkNum(w.getZcsx())){
            map.put("zcsx", "0");
        }
        if(!w.getZzc().equals("")&&!checkNum(w.getZzc())){
            map.put("zzc", "0");
        }
        if(!w.getZcjy().equals("")&&!checkNum(w.getZcjy())){
            map.put("zcjy", "0");
        }
        list.add(map);
        if(map.isEmpty()){
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            waybillMgmt = WaybillMgmt.account(waybillMgmt, w);
            waybillMapper.saveAndFlush(waybillMgmt);
            logger.info("回车报账成功"+w.getYdid());
        }
        return list;
    }

    //回车登记编辑
    @MonitorRequest
    @RequiresPermissions("232")
    @RequestMapping(value = "registerEdit", method = RequestMethod.POST)
    public List<Map<String,Object>> registerWaybillEdit(@RequestBody WaybillMgmt w){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(w.getYdid()==null){
            map.put("ydid","0");
        }else if(waybillMapper.findByYdid(w.getYdid())==null){
            map.put("ydid","1");
        }
        list.add(map);
        if(map.isEmpty()){
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            waybillMgmt = WaybillMgmt.registerEdit(waybillMgmt,w);
            waybillMapper.saveAndFlush(waybillMgmt);
            // carMapper.updateBackCarfp(waybillMgmt.getCph());
            logger.info("回车登记成功"+w.getYdid());
            // 修改订单状态
            if(orderMapper.findByDdhAndSfcz(w.getYdh(),true)!=null){
                orderMapper.UpdateByDdidzt(w.getYdh());
            }
        }
        return list;
    }

    //回车报账编辑
    @MonitorRequest
    @RequiresPermissions("242")
    @RequestMapping(value = "accountEdit", method = RequestMethod.POST)
    public List<Map<String,Object>> accountWaybillEdit(@RequestBody WaybillMgmt w){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        if(w.getYdid()==null){
            map.put("ydid","0");
        }else if(waybillMapper.findByYdid(w.getYdid())==null){
            map.put("ydid","1");
        }
        if(!w.getSrqt().equals("")&&!checkNum(w.getSrqt())){
            map.put("srqt", "0");
        }
        if(!w.getHuihuo().equals("")&&!checkNum(w.getHuihuo())){
            map.put("huihuo", "0");
        }
        if(!w.getHhbh().equals("")&&!checkNum(w.getHhbh())){
            map.put("hhbh", "0");
        }
        if(!w.getSryf().equals("")&&!checkNum(w.getSryf())){
            map.put("sryf", "0");
        }
        if(!w.getZsr().equals("")&&!checkNum(w.getZsr())){
            map.put("zsr", "0");
        }
        if(!w.getSryk().equals("")&&!checkNum(w.getSryk())){
            map.put("sryk", "0");
        }
        if(!w.getSrcy().equals("")&&!checkNum(w.getSrcy())){
            map.put("srcy", "0");
        }
        if(!w.getGlf().equals("")&&!checkNum(w.getGlf())){
            map.put("glf", "0");
        }
        if(!w.getXjjy().equals("")&&!checkNum(w.getXjjy())){
            map.put("xjjy", "0");
        }
        if(!w.getBuzhu().equals("")&&!checkNum(w.getBuzhu())){
            map.put("buzhu", "0");
        }
        if(!w.getMaidan().equals("")&&!checkNum(w.getMaidan())){
            map.put("maidan", "0");
        }
        if(!w.getSgcl().equals("")&&!checkNum(w.getSgcl())){
            map.put("sgcl", "0");
        }
        if(!w.getTcf().equals("")&&!checkNum(w.getTcf())){
            map.put("tcf", "0");
        }
        if(!w.getZsf().equals("")&&!checkNum(w.getZsf())){
            map.put("zsf", "0");
        }
        if(!w.getZxf().equals("")&&!checkNum(w.getZxf())){
            map.put("zxf", "0");
        }
        if(!w.getChuanpiao().equals("")&&!checkNum(w.getChuanpiao())){
            map.put("chuanpiao", "0");
        }
        if(!w.getWzfk().equals("")&&!checkNum(w.getWzfk())){
            map.put("wzfk", "0");
        }
        if(!w.getPeijian().equals("")&&!checkNum(w.getPeijian())){
            map.put("peijian", "0");
        }
        if(!w.getZcqt().equals("")&&!checkNum(w.getZcqt())){
            map.put("zcqt", "0");
        }
        if(!w.getWxfycn().equals("")&&!checkNum(w.getWxfycn())){
            map.put("wxfycn", "0");
        }
        if(!w.getWxfycw().equals("")&&!checkNum(w.getWxfycw())){
            map.put("wxfycw", "0");
        }
        if(!w.getZcsx().equals("")&&!checkNum(w.getZcsx())){
            map.put("zcsx", "0");
        }
        if(!w.getZzc().equals("")&&!checkNum(w.getZzc())){
            map.put("zzc", "0");
        }
        if(!w.getZcjy().equals("")&&!checkNum(w.getZcjy())){
            map.put("zcjy", "0");
        }
        list.add(map);
        if(map.isEmpty()){
            WaybillMgmt waybillMgmt = waybillMapper.findByYdid(w.getYdid());
            waybillMgmt = WaybillMgmt.accountEdit(waybillMgmt, w);
            waybillMapper.saveAndFlush(waybillMgmt);
            logger.info("回车报账成功"+w.getYdid());
        }
        return list;
    }


    //快捷运单
    @MonitorRequest
    @RequiresPermissions("252")
    @RequestMapping(value = "shortcut", method = RequestMethod.POST)
    public List<Map<String,Object>> shortcutWaybill(@RequestBody WaybillMgmt waybillMgmt){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map = new HashMap<>();
        list.add(map);
        if(map.isEmpty()) {
//            String ydh = waybillMgmt.getYdrq().replaceAll("-","");
//            long i = orderMapper.countByDdrq(waybillMgmt.getYdrq());
//            OrderMgmt orderMgmt = new OrderMgmt(waybillMgmt, i);
//            orderMapper.save(orderMgmt);
//            waybillMgmt.setYdh(ydh+GlobalParam.getDdh(i));
            if(waybillMgmt.getYdrq().equals("")){
                waybillMgmt.setYdrq(GlobalParam.getSRq());
            }
            long i = orderMapper.countByDdrq(waybillMgmt.getYdrq());
            CarInfo carInfo = carMapper.findByClid(waybillMgmt.getClid());
            CstmrInfo cstmrInfo = cstmrMapper.findByKhid(waybillMgmt.getKhid());
            AreaInfo areaInfo = areaMapper.findByQyidAndSfcz(waybillMgmt.getQyid(),true);
            RoadInfo roadInfo = roadMapper.findByXlid(waybillMgmt.getXlid());
            OrderMgmt orderMgmt = new OrderMgmt(waybillMgmt, cstmrInfo, areaInfo, roadInfo, i);
            orderMapper.save(orderMgmt);
            waybillMgmt = WaybillMgmt.shortcut(waybillMgmt,cstmrInfo,areaInfo,roadInfo,carInfo);
            waybillMgmt.setYdh(orderMgmt.getDdh());
            waybillMapper.save(waybillMgmt);
            logger.info("新建快捷运单"+waybillMgmt.toString());
//            OilfillInfo OilfillInfo = new OilfillInfo(waybillMgmt);//生成充油信息
//            try{
//                OilfillInfo.setFcrq(GlobalParam.getSRq());
//                oilfillMapper.save(OilfillInfo);
//            }catch (NullPointerException e){
//                e.printStackTrace();
//            }
        }
        return list;
    }

    //运单excel下载
//    @RequiresPermissions("313")
    @RequestMapping(value = "/waybillexcel", method = RequestMethod.GET)
    public ReturnInfo getWaybillExcel(HttpServletResponse response, HttpServletRequest request){
        String ylzl = "";
        String cph = "";
        String jsyxm = "";
        String khjc = "";
        String khzjm = "";
        String xlmc = "";
        String xlzjm = "";
        String qymc = "";
        String qyzjm = "";
        String ydh = "";
        String ydzt = "";
        String qsrq = "";
        String jsrq = "";
        String bd = "";
        if(request.getParameter("ylzl")!=null&&!request.getParameter("ylzl").equals("")){
            ylzl = request.getParameter("ylzl");
        }
        if(request.getParameter("cph")!=null&&!request.getParameter("cph").equals("")){
            cph = request.getParameter("cph");
        }
        if(request.getParameter("jsyxm")!=null&&!request.getParameter("jsyxm").equals("")){
            jsyxm = request.getParameter("jsyxm");
        }
        if(request.getParameter("khjc")!=null&&!request.getParameter("khjc").equals("")){
            khjc = request.getParameter("khjc");
        }
        if(request.getParameter("khzjm")!=null&&!request.getParameter("khzjm").equals("")){
            khzjm = request.getParameter("khzjm");
        }
        if(request.getParameter("xlmc")!=null&&!request.getParameter("xlmc").equals("")){
            xlmc = request.getParameter("xlmc");
        }
        if(request.getParameter("xlzjm")!=null&&!request.getParameter("xlzjm").equals("")){
            xlzjm = request.getParameter("xlzjm");
        }
        if(request.getParameter("qymc")!=null&&!request.getParameter("qymc").equals("")){
            qymc = request.getParameter("qymc");
        }
        if(request.getParameter("qyzjm")!=null&&!request.getParameter("qyzjm").equals("")){
            qyzjm = request.getParameter("qyzjm");
        }
        if(request.getParameter("ydh")!=null&&!request.getParameter("ydh").equals("")){
            ydh = request.getParameter("ydh");
        }
        if(request.getParameter("ydzt")!=null&&!request.getParameter("ydzt").equals("")){
            ydzt = request.getParameter("ydzt");
        }
        if(request.getParameter("qsrq")!=null&&!request.getParameter("qsrq").equals("")){
            qsrq = request.getParameter("qsrq");
        }
        if(request.getParameter("jsrq")!=null&&!request.getParameter("jsrq").equals("")){
            jsrq = request.getParameter("jsrq");
        }
        if(request.getParameter("bd")!=null&&!request.getParameter("bd").equals("")){
            bd = request.getParameter("bd");
        }
        String classPath = this.getClass().getClassLoader().getResource("").getPath();
        String path = classPath+"static/excel/";
        path=path.replaceAll("%20"," ");
        String filename = GlobalParam.getSSj()+".xls";
        String newpath = path+ filename;
        try {
            creatWaybillExcel(getWaybillListexcel(ylzl,cph,jsyxm,khjc,khzjm,xlmc,xlzjm,qymc,qyzjm,ydh,ydzt,qsrq,jsrq,bd),newpath,qsrq,jsrq);
        }catch (IOException e){
            e.printStackTrace();
        }
        File newfile = new File(newpath);
        logger.info("运单表"+filename+" 创建成功！");
        if(newfile.exists()){
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/force-download");// 设置强制下载不打开
            response.addHeader("Content-Disposition",
                    "attachment;fileName=" + filename);// 设置文件名
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {
                fis = new FileInputStream(newfile);
                bis = new BufferedInputStream(fis);
                OutputStream os = response.getOutputStream();
                int i = bis.read(buffer);
                while (i != -1) {
                    os.write(buffer, 0, i);
                    i = bis.read(buffer);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        //文件删除
        if(newfile.delete()){
            logger.info("运单表"+filename+" 删除成功！");
        }else {
            logger.info("运单表"+filename+" 删除失败！");
        }
        return null;
    }

    private void creatWaybillExcel(List<WaybillVMgmt> waybillVMgmts, String newpath, String qsrq, String jsrq)throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFSheet sheet=wb.createSheet("运单表");
        sheet.setColumnWidth(0,13*256);
        sheet.setColumnWidth(1,13*256);
        sheet.setColumnWidth(2,13*256);
        sheet.setColumnWidth(3,13*256);
        sheet.setColumnWidth(5,25*256);
        sheet.setColumnWidth(7,13*256);
        sheet.setColumnWidth(8,13*256);
        sheet.setColumnWidth(14,16*256);
        sheet.setColumnWidth(15,16*256);
        sheet.setColumnWidth(17,13*256);
        sheet.setColumnWidth(20,13*256);


        for(int x = 0; x<3+waybillVMgmts.size(); x++){
            // 创建新建excel Sheet的行
            HSSFRow row = sheet.createRow(x);
            // 单元格式样
            for (int y = 0; y<=65; y++) {
                row.createCell(y);
                row.getCell(y).setCellStyle(cellStyle);
            }
        }
        HSSFRow row1=sheet.getRow(0);
        row1.setHeight((short)(25*20));
        row1.getCell(0).setCellValue("运单表");
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,30));
        HSSFRow row2=sheet.getRow(1);
        row2.setHeight((short)(15*20));
        row2.getCell(0).setCellValue("统计日期："+qsrq+"至"+jsrq);
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,30));
        HSSFRow row3=sheet.getRow(2);
        row3.setHeight((short)(25*20));
        row3.getCell(0).setCellValue("运单号");
        row3.getCell(1).setCellValue("运单日期");
        row3.getCell(2).setCellValue("运单状态");
        row3.getCell(3).setCellValue("客户简称");
        row3.getCell(4).setCellValue("客户助记码");
        row3.getCell(5).setCellValue("线路简称");
        row3.getCell(6).setCellValue("线路助记码");
        row3.getCell(7).setCellValue("区域简称");
        row3.getCell(8).setCellValue("区域助记码");
        row3.getCell(9).setCellValue("货物");
        row3.getCell(10).setCellValue("货物数量");
        row3.getCell(11).setCellValue("运费");
        row3.getCell(12).setCellValue("油单价");
        row3.getCell(13).setCellValue("公里数");
        row3.getCell(14).setCellValue("车牌号");
        row3.getCell(15).setCellValue("车辆类型");
        row3.getCell(16).setCellValue("驾驶员姓名");
        row3.getCell(17).setCellValue("驾驶员手机");
        row3.getCell(18).setCellValue("百公里油耗");
        row3.getCell(19).setCellValue("理论充油金额");
        row3.getCell(20).setCellValue("发车日期");
        row3.getCell(21).setCellValue("类型");
        row3.getCell(22).setCellValue("速通卡");
        row3.getCell(23).setCellValue("回车日期");
        row3.getCell(24).setCellValue("收货方");
        row3.getCell(25).setCellValue("车标号");
        row3.getCell(26).setCellValue("实发量");
        row3.getCell(27).setCellValue("实收量");
        row3.getCell(28).setCellValue("实际运费");
        row3.getCell(29).setCellValue("损耗金额");
        row3.getCell(30).setCellValue("代垫费");
        row3.getCell(31).setCellValue("实际公里数");
        row3.getCell(32).setCellValue("应收金额");
        row3.getCell(33).setCellValue("实收金额");
        row3.getCell(34).setCellValue("是否准点");
        row3.getCell(35).setCellValue("报账日期");
        row3.getCell(36).setCellValue("充油卡");
        row3.getCell(37).setCellValue("油卡号");
        row3.getCell(38).setCellValue("实际充油金额");
        row3.getCell(39).setCellValue("其他收入");
        row3.getCell(40).setCellValue("回货");
        row3.getCell(41).setCellValue("回货补回");
        row3.getCell(42).setCellValue("收入运费");
        row3.getCell(43).setCellValue("总收入");
        row3.getCell(44).setCellValue("收入项油卡");
        row3.getCell(45).setCellValue("收入项超油");
        row3.getCell(46).setCellValue("过路费");
        row3.getCell(47).setCellValue("现金加油");
        row3.getCell(48).setCellValue("线路补助");
        row3.getCell(49).setCellValue("买单");
        row3.getCell(50).setCellValue("事故处理");
        row3.getCell(51).setCellValue("停车费");
        row3.getCell(52).setCellValue("住宿费");
        row3.getCell(53).setCellValue("装卸费");
        row3.getCell(54).setCellValue("船票");
        row3.getCell(55).setCellValue("违章罚款");
        row3.getCell(56).setCellValue("配件");
        row3.getCell(57).setCellValue("其他支出");
        row3.getCell(58).setCellValue("维修费用(场内)");
        row3.getCell(59).setCellValue("维修费用(场外)");
        row3.getCell(60).setCellValue("支出上限");
        row3.getCell(61).setCellValue("总支出");
        row3.getCell(62).setCellValue("回程线路");
        row3.getCell(63).setCellValue("回程日期");
        row3.getCell(64).setCellValue("回程公里数");
        row3.getCell(65).setCellValue("回程理论充油值");
        JSONArray ja = JSONArray.parseArray(JSON.toJSONString(waybillVMgmts));
        int i = 3;
        for(Object aJa : ja){
            JSONObject job = (JSONObject) aJa;
            HSSFRow row = sheet.getRow(i);
            row.setHeight((short)(20*20));
            if(job.get("ydh")!=null){
                row.getCell(0).setCellValue(job.get("ydh").toString());
            }else {
                row.getCell(0).setCellValue("");
            }
            if(job.get("ydrq")!=null){
                row.getCell(1).setCellValue(job.get("ydrq").toString());
            }else{
                row.getCell(1).setCellValue("");
            }
            if(job.get("ydzt")!=null){
                row.getCell(2).setCellValue(job.get("ydzt").toString());
            }else{
                row.getCell(2).setCellValue("");
            }
            if(job.get("khjc")!=null){
                row.getCell(3).setCellValue(job.get("khjc").toString());
            }else{
                row.getCell(3).setCellValue("");
            }
            if(job.get("khzjm")!=null){
                row.getCell(4).setCellValue(job.get("khzjm").toString());
            }else{
                row.getCell(4).setCellValue("");
            }
            if(job.get("xlmc")!=null){
                row.getCell(5).setCellValue(job.get("xlmc").toString());
            }else{
                row.getCell(5).setCellValue("");
            }
            if(job.get("xlzjm")!=null){
                row.getCell(6).setCellValue(job.get("xlzjm").toString());
            }else{
                row.getCell(6).setCellValue("");
            }
            if(job.get("qymc")!=null){
                row.getCell(7).setCellValue(job.get("qymc").toString());
            }else{
                row.getCell(7).setCellValue("");
            }
            if(job.get("zjm")!=null){
                row.getCell(8).setCellValue(job.get("zjm").toString());
            }else{
                row.getCell(8).setCellValue("");
            }
            if(job.get("huowu")!=null){
                row.getCell(9).setCellValue(job.get("huowu").toString());
            }else{
                row.getCell(9).setCellValue("");
            }
            if(job.get("hwsl")!=null){
                row.getCell(10).setCellValue(job.get("hwsl").toString());
            }else{
                row.getCell(10).setCellValue("");
            }
            if(job.get("yunfei")!=null){
                row.getCell(11).setCellValue(job.get("yunfei").toString());
            }else{
                row.getCell(11).setCellValue("");
            }
            if(job.get("ydj")!=null){
                row.getCell(12).setCellValue(job.get("ydj").toString());
            }else{
                row.getCell(12).setCellValue("");
            }
            if(job.get("gls")!=null){
                row.getCell(13).setCellValue(job.get("gls").toString());
            }else{
                row.getCell(13).setCellValue("");
            }
            if(job.get("cph")!=null){
                row.getCell(14).setCellValue(job.get("cph").toString());
            }else{
                row.getCell(14).setCellValue("");
            }
            if(job.get("cllx")!=null){
                row.getCell(15).setCellValue(job.get("cllx").toString());
            }else{
                row.getCell(15).setCellValue("");
            }
            if(job.get("jsyxm")!=null){
                row.getCell(16).setCellValue(job.get("jsyxm").toString());
            }else{
                row.getCell(16).setCellValue("");
            }
            if(job.get("jsysj")!=null){
                row.getCell(17).setCellValue(job.get("jsysj").toString());
            }else{
                row.getCell(17).setCellValue("");
            }
            if(job.get("bglyh")!=null){
                row.getCell(18).setCellValue(job.get("bglyh").toString());
            }else{
                row.getCell(18).setCellValue("");
            }
            if(job.get("llcyje")!=null){
                row.getCell(19).setCellValue(job.get("llcyje").toString());
            }else{
                row.getCell(19).setCellValue("");
            }
            if(job.get("fcrq")!=null){
                row.getCell(20).setCellValue(job.get("fcrq").toString());
            }else{
                row.getCell(20).setCellValue("");
            }
            if(job.get("leixing")!=null){
                row.getCell(21).setCellValue(job.get("leixing").toString());
            }else{
                row.getCell(21).setCellValue("");
            }
            if(job.get("stk")!=null){
                row.getCell(22).setCellValue(job.get("stk").toString());
            }else{
                row.getCell(22).setCellValue("");
            }
            if(job.get("hcrq")!=null){
                row.getCell(23).setCellValue(job.get("hcrq").toString());
            }else{
                row.getCell(23).setCellValue("");
            }
            if(job.get("shf")!=null){
                row.getCell(24).setCellValue(job.get("shf").toString());
            }else{
                row.getCell(24).setCellValue("");
            }
            if(job.get("hdh")!=null){
                row.getCell(25).setCellValue(job.get("hdh").toString());
            }else{
                row.getCell(25).setCellValue("");
            }
            if(job.get("sfl")!=null){
                row.getCell(26).setCellValue(job.get("sfl").toString());
            }else{
                row.getCell(26).setCellValue("");
            }
            if(job.get("hcssl")!=null){
                row.getCell(27).setCellValue(job.get("hcssl").toString());
            }else{
                row.getCell(27).setCellValue("");
            }
            if(job.get("sjyf")!=null){
                row.getCell(28).setCellValue(job.get("sjyf").toString());
            }else{
                row.getCell(28).setCellValue("");
            }
            if(job.get("shje")!=null){
                row.getCell(29).setCellValue(job.get("shje").toString());
            }else{
                row.getCell(29).setCellValue("");
            }
            if(job.get("ddf")!=null){
                row.getCell(30).setCellValue(job.get("ddf").toString());
            }else{
                row.getCell(30).setCellValue("");
            }
            if(job.get("sjgls")!=null){
                row.getCell(31).setCellValue(job.get("sjgls").toString());
            }else{
                row.getCell(31).setCellValue("");
            }
            if(job.get("ysje")!=null){
                row.getCell(32).setCellValue(job.get("ysje").toString());
            }else{
                row.getCell(32).setCellValue("");
            }
            if(job.get("shje")!=null){
                row.getCell(33).setCellValue(job.get("shje").toString());
            }else{
                row.getCell(33).setCellValue("");
            }
            if(job.get("sfzd")!=null){
                row.getCell(34).setCellValue(job.get("sfzd").toString());
            }else{
                row.getCell(34).setCellValue("");
            }
            if(job.get("bzrq")!=null){
                row.getCell(35).setCellValue(job.get("bzrq").toString());
            }else{
                row.getCell(35).setCellValue("");
            }
            if(job.get("cyk")!=null){
                row.getCell(36).setCellValue(job.get("cyk").toString());
            }else{
                row.getCell(36).setCellValue("");
            }
            if(job.get("ykh")!=null){
                row.getCell(37).setCellValue(job.get("ykh").toString());
            }else{
                row.getCell(37).setCellValue("");
            }
            if(job.get("sjcyje")!=null){
                row.getCell(38).setCellValue(job.get("sjcyje").toString());
            }else{
                row.getCell(38).setCellValue("");
            }
            if(job.get("srqt")!=null){
                row.getCell(39).setCellValue(job.get("srqt").toString());
            }else{
                row.getCell(39).setCellValue("");
            }
            if(job.get("huihuo")!=null){
                row.getCell(40).setCellValue(job.get("huihuo").toString());
            }else{
                row.getCell(40).setCellValue("");
            }
            if(job.get("hhbh")!=null){
                row.getCell(41).setCellValue(job.get("hhbh").toString());
            }else{
                row.getCell(41).setCellValue("");
            }
            if(job.get("sryf")!=null){
                row.getCell(42).setCellValue(job.get("sryf").toString());
            }else{
                row.getCell(42).setCellValue("");
            }
            if(job.get("zsr")!=null){
                row.getCell(43).setCellValue(job.get("zsr").toString());
            }else{
                row.getCell(43).setCellValue("");
            }
            if(job.get("sryk")!=null){
                row.getCell(44).setCellValue(job.get("sryk").toString());
            }else{
                row.getCell(44).setCellValue("");
            }
            if(job.get("srcy")!=null){
                row.getCell(45).setCellValue(job.get("srcy").toString());
            }else{
                row.getCell(45).setCellValue("");
            }
            if(job.get("glf")!=null){
                row.getCell(46).setCellValue(job.get("glf").toString());
            }else{
                row.getCell(46).setCellValue("");
            }
            if(job.get("xjjy")!=null){
                row.getCell(47).setCellValue(job.get("xjjy").toString());
            }else{
                row.getCell(47).setCellValue("");
            }
            if(job.get("buzhu")!=null){
                row.getCell(48).setCellValue(job.get("buzhu").toString());
            }else{
                row.getCell(48).setCellValue("");
            }
            if(job.get("maidan")!=null){
                row.getCell(49).setCellValue(job.get("maidan").toString());
            }else{
                row.getCell(49).setCellValue("");
            }
            if(job.get("sgcl")!=null){
                row.getCell(50).setCellValue(job.get("sgcl").toString());
            }else{
                row.getCell(50).setCellValue("");
            }
            if(job.get("tcf")!=null){
                row.getCell(51).setCellValue(job.get("tcf").toString());
            }else{
                row.getCell(51).setCellValue("");
            }
            if(job.get("zsf")!=null){
                row.getCell(52).setCellValue(job.get("zsf").toString());
            }else{
                row.getCell(52).setCellValue("");
            }
            if(job.get("zxf")!=null){
                row.getCell(53).setCellValue(job.get("zxf").toString());
            }else{
                row.getCell(53).setCellValue("");
            }
            if(job.get("chuanpiao")!=null){
                row.getCell(54).setCellValue(job.get("chuanpiao").toString());
            }else{
                row.getCell(54).setCellValue("");
            }
            if(job.get("wzfk")!=null){
                row.getCell(55).setCellValue(job.get("wzfk").toString());
            }else{
                row.getCell(55).setCellValue("");
            }
            if(job.get("peijian")!=null){
                row.getCell(56).setCellValue(job.get("peijian").toString());
            }else{
                row.getCell(56).setCellValue("");
            }
            if(job.get("zcqt")!=null){
                row.getCell(57).setCellValue(job.get("zcqt").toString());
            }else{
                row.getCell(57).setCellValue("");
            }
            if(job.get("wxfycn")!=null){
                row.getCell(58).setCellValue(job.get("wxfycn").toString());
            }else{
                row.getCell(58).setCellValue("");
            }
            if(job.get("wxfycw")!=null){
                row.getCell(59).setCellValue(job.get("wxfycw").toString());
            }else{
                row.getCell(59).setCellValue("");
            }
            if(job.get("zcsx")!=null){
                row.getCell(60).setCellValue(job.get("zcsx").toString());
            }else{
                row.getCell(60).setCellValue("");
            }
            if(job.get("zzc")!=null){
                row.getCell(61).setCellValue(job.get("zzc").toString());
            }else{
                row.getCell(61).setCellValue("");
            }
            if(job.get("hcxl")!=null){
                row.getCell(62).setCellValue(job.get("hcxl").toString());
            }else{
                row.getCell(62).setCellValue("");
            }
            if(job.get("backdate")!=null){
                row.getCell(63).setCellValue(job.get("backdate").toString());
            }else{
                row.getCell(63).setCellValue("");
            }
            if(job.get("hcgls")!=null){
                row.getCell(64).setCellValue(job.get("hcgls").toString());
            }else{
                row.getCell(64).setCellValue("");
            }
            if(job.get("hcllcyz")!=null){
                row.getCell(65).setCellValue(job.get("hcllcyz").toString());
            }else{
                row.getCell(65).setCellValue("");
            }
            i++;
        }
        FileOutputStream out =new FileOutputStream(newpath);
        wb.write(out);
        out.close();
    }

    //查询运单数量
    @RequestMapping(value = "waybillNum", method = RequestMethod.GET)
    public ReturnInfo getWaybillNum(){
        List<Map<String,Object>>  list = new ArrayList<>();
        Map<String,Object>  map1 = new HashMap<>();
        Map<String,Object>  map2 = new HashMap<>();
        Map<String,Object>  map3 = new HashMap<>();
        Map<String,Object>  map4 = new HashMap<>();
        Map<String,Object>  map5 = new HashMap<>();
        Map<String,Object>  map6 = new HashMap<>();
        Map<String,Object>  map7 = new HashMap<>();
        Map<String,Object>  map8 = new HashMap<>();
        Map<String,Object>  map9 = new HashMap<>();
        Integer waybillDdd = waybillMapper.findYdddd();
        Integer waybillDfc = waybillMapper.findYddfc();
        Integer waybillYwc = waybillMapper.findYdywc();
        Integer waybillYhc = waybillMapper.findYdyhc();
        Integer waybillYfc = waybillMapper.findYdyfc();
        Integer waybillShz = waybillMapper.findYdshz();
        Integer waybillCx = waybillMapper.findYdcx();
        Integer waybillYbz = waybillMapper.findYdybz();
        Integer waybillYb = waybillMapper.findYdyb();
        if(waybillDdd>0){
            map1.put("department","待调度");
            map1.put("num",waybillDdd);
            list.add(map1);
        }
        if(waybillDfc>0){
            map2.put("department","待发车");
            map2.put("num",waybillDfc);
            list.add(map2);
        }
        if(waybillYwc>0){
            map3.put("department","已完成");
            map3.put("num",waybillYwc);
            list.add(map3);
        }
        if(waybillYhc>0){
            map4.put("department","已回车");
            map4.put("num",waybillYhc);
            list.add(map4);
        }
        if(waybillYfc>0){
            map5.put("department","已发车");
            map5.put("num",waybillYfc);
            list.add(map5);
        }
        if(waybillShz>0){
            map6.put("department","审核中");
            map6.put("num",waybillShz);
            list.add(map6);
        }
        if(waybillCx>0){
            map7.put("department","撤销");
            map7.put("num",waybillCx);
            list.add(map7);
        }
        if(waybillYbz>0){
            map8.put("department","已报账");
            map8.put("num",waybillYbz);
            list.add(map8);
        }
        if(waybillYb>0){
            map9.put("department","已报");
            map9.put("num",waybillYb);
            list.add(map9);
        }
        return ReturnInfo.info2(list);
    }






    //检查数字格式
    private Boolean checkNum(String  num){
        Boolean b = false;
        if(num.matches("^([0-9]\\d*(\\.\\d*[0-9])?)|(0\\.\\d*[1-9])|0.00$")){
            b = true;
        }
        return b;
    }
}
